package DataBase;
import fingerprint.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * Created by 钟润东 on 2016/11/15.
 */
public class API {

    //插入
    public static void Insert(String song_name , ArrayList<ShazamHash> hash) throws Exception{

        Connection con = ConnectDB();
        //插入歌曲
        String sql = "insert into db.song(Id,name) values(0,'" + song_name + "')";
        Statement st = con.createStatement();
        st.executeUpdate(sql);
        //获取歌曲Id
        sql = "select * from db.song WHERE name = '" + song_name + "'";
        ResultSet rs = st.executeQuery(sql);
        rs.next();int song_Id = rs.getInt(1);
        //插入hash
        sql = "insert into db.song_finger values";

        System.out.println("正在导入数据......");
        try{
            int n = hash.size() - 1;
            int finger_Id = 0;
            for(int i = 0;i < n;i++){
                ShazamHash h = hash.get(i);
                finger_Id = (h.dt<<18) | (h.f1<<9) | h.f2;
                sql = sql + "(" + song_Id + "," + finger_Id + "," + h.offset + "),";
            }
            ShazamHash h = hash.get(n);
            finger_Id = (h.dt<<18) | (h.f1<<9) | h.f2;
            sql = sql + "(" + song_Id + "," + finger_Id + "," + h.offset + ")";
            st.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println("完成");
    }

    //查询
    public static ArrayList<ShazamHash> Query(ArrayList<ShazamHash> hash) throws Exception{
        Connection con = ConnectDB();
        Statement st = con.createStatement();
        int n = hash.size();ArrayList<ShazamHash> result = new ArrayList<ShazamHash>();
        ShazamHash h = hash.get(0);
        int finger_Id = (h.dt << 18) | (h.f1 << 9) | h.f2;
        String sql = "select song_Id,offset from db.song_finger force index(finger_Id)" +
                "where finger_Id = " + finger_Id + " ";

        for(int i = 1;i < n;i++) {
            h = hash.get(i);
            finger_Id = (h.dt << 18) | (h.f1 << 9) | h.f2;
            sql = sql + "or finger_Id = " + finger_Id + " ";
        }
        ResultSet rs = st.executeQuery(sql);
        //System.out.println("查询完毕");
        while (rs.next()) {
            h = new ShazamHash();
            h.id = rs.getInt(1);
            h.offset = rs.getInt(2);
            result.add(h);
        }
        return result;
    }
    /*public static ArrayList<ShazamHash> Query(ArrayList<ShazamHash> hash) throws Exception{
        Connection con = ConnectDB();
        Statement st = con.createStatement();
        int n = hash.size();ArrayList<ShazamHash> result = new ArrayList<ShazamHash>();
        for(int i = 0;i < n;i++) {
            ShazamHash h = hash.get(i);
            int finger_Id = (h.dt << 18) | (h.f1 << 9) | h.f2;
            String sql = "select song_Id,offset - " + h.offset + " from db.song_finger force index(finger_Id)" +
                    "where finger_Id = " + finger_Id + " ";
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                h = new ShazamHash();
                h.id = rs.getInt(1);
                h.offset = rs.getInt(2);
                result.add(h);
            }
        }
        return result;
    }*/

    //由歌曲Id获取名字
    public static String getName(int song_Id) throws Exception{
        Connection con = ConnectDB();
        Statement st = con.createStatement();
        String sql = "select * from db.song where Id = " + song_Id;
        ResultSet rs = st.executeQuery(sql);
        String name = "";
        while (rs.next())
            name = rs.getString(2);
        return name;
    }

    //建立连接
    public static Connection ConnectDB(){
        String drivername = "com.mysql.jdbc.Driver";
        try{
            Class.forName(drivername);
            //System.out.println("创建驱动成功");
        }catch(Exception e){
            e.printStackTrace();
        }

        Connection con = null;
        String url = "jdbc:mysql://localhost:3306/db?useSSL=false";
        String user = "root";
        String password = "root";
        try{
            con = DriverManager.getConnection(url,user,password);
            //System.out.println("链接数据库成功");
        }catch (SQLException e){
            e.printStackTrace();
        }
        return con;
    }

    //清空数据库
    public static void Clear()throws Exception{
        Connection con = ConnectDB();
        String sql = "delete from db.song_finger";
        Statement st = con.createStatement();
        st.executeUpdate(sql);
        sql = "delete from db.song";
        st.executeUpdate(sql);
        System.out.println("清空完成");
    }

    //获取歌曲名单
    public static ArrayList<String> SongList() throws Exception{
        ArrayList<String> songList = new ArrayList<String>();String str = "";
        Connection con = ConnectDB();
        String sql = "select * from db.song";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while(rs.next()){
            str = rs.getString(2);
            songList.add(str);
        }
        return songList;
    }
}
